﻿
CREATE PROCEDURE DeleteTool
	@ToolID int

AS
BEGIN

	SET NOCOUNT ON;

	/* Identify FileStream files that will need to be deleted */
	DECLARE @DelFiles TABLE (stream_id uniqueidentifier);
	INSERT INTO @DelFiles (stream_id) 
		SELECT stream_id FROM ToolFileMap 
		WHERE ToolID = @ToolID;

	/* Delete the filemap and then the file */
	DELETE ToolFileMap WHERE ToolID = @ToolID;
	DELETE ToolFile WHERE stream_id IN (SELECT stream_id FROM @DelFiles);

	/* Delete the rest of the children tables */
	DELETE ToolScript WHERE ToolID = @ToolID;
	DELETE ToolNote WHERE ToolID = @ToolID;
	DELETE ToolTag WHERE ToolID = @ToolID;
	DELETE ToolURL WHERE ToolID = @ToolID;

	/* Finally delete the tool record */
	DELETE Tool WHERE ToolID = @ToolID;

END